-- 初始化网关数据库表结构

-- 创建网关配置表
CREATE TABLE IF NOT EXISTS gateway_configs (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE,
    target_url VARCHAR(500) NOT NULL,
    is_active BOOLEAN DEFAULT false,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建路由规则表
CREATE TABLE IF NOT EXISTS route_rules (
    id SERIAL PRIMARY KEY,
    path_pattern VARCHAR(500) NOT NULL,
    target_url VARCHAR(500) NOT NULL,
    method VARCHAR(10) DEFAULT 'GET',
    gateway_config_id INTEGER REFERENCES gateway_configs(id),
    is_active BOOLEAN DEFAULT true,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建用户表（如果项目需要用户管理）
CREATE TABLE IF NOT EXISTS users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(100) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    role VARCHAR(50) DEFAULT 'user',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建索引以提高查询性能
CREATE INDEX IF NOT EXISTS idx_gateway_configs_active ON gateway_configs(is_active);
CREATE INDEX IF NOT EXISTS idx_route_rules_active ON route_rules(is_active);
CREATE INDEX IF NOT EXISTS idx_route_rules_gateway_id ON route_rules(gateway_config_id);
CREATE INDEX IF NOT EXISTS idx_users_username ON users(username);

-- 插入示例数据
INSERT INTO gateway_configs (name, target_url, is_active) VALUES
('api-service', 'http://api.example.com', true),
('web-service', 'http://web.example.com', false)
ON CONFLICT (name) DO NOTHING;

INSERT INTO route_rules (path_pattern, target_url, method, gateway_config_id, is_active) VALUES
('/api/v1/*', 'http://api.example.com/api/v1', 'ANY', 1, true),
('/web/*', 'http://web.example.com', 'GET', 2, true)
ON CONFLICT DO NOTHING;

-- 创建更新时间的触发器函数
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

-- 为每个表创建触发器
CREATE TRIGGER update_gateway_configs_updated_at BEFORE UPDATE ON gateway_configs FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_route_rules_updated_at BEFORE UPDATE ON route_rules FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
CREATE TRIGGER update_users_updated_at BEFORE UPDATE ON users FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();